﻿Imports System.Data.SqlClient

Public Class frmInsuranceClasses

    Sub FillInsurCompanies()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim InsurCompanies As ArrayList = GetInsurCompaniesList()

            Me.CombClass.Items.Clear()

            For Each InsurComp As String In InsurCompanies
                Me.CombClass.Items.Add(InsurComp)
            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub FillClasses()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select * From InsurClasses", cnn)
            Dim Reader As SqlDataReader

            Me.GridInsuranceClasses.Rows.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                Me.GridInsuranceClasses.Rows.Add(New String() {
                                                 Reader.Item("SNo"), Reader.Item("ClassName"), Reader.Item("LineOfBusiness"), _
                                                 Reader.Item("Abbreviation"), Reader.Item("AccNo"), GetAccName(Reader.Item("AccNo"))})
            End While
            cnn.Close()

            Me.lblResult.Text = "Count: " & Me.GridInsuranceClasses.Rows.Count

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub Clear()
        Me.ErrProv.Clear()
        Me.txtLineOfBusiness.Clear()
        Me.CombClass.SelectedIndex = -1
        Me.txtAbbreviation.Clear()
        Me.txtAccNo.Clear()
        Me.txtLineOfBusiness.Focus()
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Me.ErrProv.Clear()
        If Me.CombClass.SelectedIndex = -1 Then
            Me.ErrProv.SetError(Me.CombClass, "Please fill in all details")
            Me.CombClass.Focus()

        ElseIf Me.txtLineOfBusiness.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtLineOfBusiness, "Please fill in all details")
            Me.txtLineOfBusiness.Focus()

        ElseIf Me.txtAbbreviation.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtAbbreviation, "Please fill in all details")
            Me.txtAbbreviation.Focus()

        ElseIf Me.txtAccName.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtAccNo, "Please fill in all details")
            Me.txtAccNo.Focus()

        Else
            Try
                Me.Cursor = Cursors.WaitCursor

                Dim cmd As New SqlCommand("Insert Into InsurClasses (ClassName,LineOfBusiness,Abbreviation,AccNo) Values " & _
                                          "(@ClassName,@LineOfBusiness,@Abbreviation,@AccNo)", cnn)

                cnn.Open()
                cmd.Parameters.AddWithValue("@ClassName", Me.CombClass.Text.Trim)
                cmd.Parameters.AddWithValue("@LineOfBusiness", Me.txtLineOfBusiness.Text.Trim)
                cmd.Parameters.AddWithValue("@Abbreviation", Me.txtAbbreviation.Text.Trim)
                cmd.Parameters.AddWithValue("@AccNo", Me.txtAccNo.Text.Trim)
                cmd.ExecuteNonQuery()
                cnn.Close()

                FillClasses()

                Clear()

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Private Sub frmIncurClassesList_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.WindowState = FormWindowState.Maximized
        FillInsurCompanies()
        FillClasses()
    End Sub

    Private Sub btnClose_Click(sender As System.Object, e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

    Private Sub GridReasons_CellClick(sender As System.Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridInsuranceClasses.CellClick
        If e.ColumnIndex = Me.DGColSearch.Index Then
            Try
                Dim a As New frmSelectAccount
                a.ShowDialog()

                If SelAccNo <> "" Then
                    Me.Cursor = Cursors.WaitCursor

                    Me.GridInsuranceClasses.CurrentRow.Cells("DGColAccNo").Value = SelAccNo
                    Me.GridInsuranceClasses.CurrentRow.Cells(Me.DGColAccName.Index).Value = GetAccName(SelAccNo)

                    Me.Cursor = Cursors.Default
                End If
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                MsgBox(ex.ToString)
            End Try

        ElseIf e.ColumnIndex = Me.DGColUpdate.Index Then
            Try
                Me.Cursor = Cursors.WaitCursor

                Dim cmd As New SqlCommand
                Dim Trans As SqlTransaction

                cnn.Open()
                Trans = cnn.BeginTransaction
                cmd.Connection = cnn
                cmd.Transaction = Trans

                'Save Contacts
                For Each row As DataGridViewRow In Me.GridInsuranceClasses.Rows
                    cmd.Parameters.Clear()
                    cmd.CommandText = "Update InsurClasses Set LineOfBusiness=@LineOfBusiness,Abbreviation=@Abbreviation " & _
                                      "Where SNo=" & row.Cells(0).Value

                    cmd.Parameters.AddWithValue("@LineOfBusiness", row.Cells(2).Value)
                    cmd.Parameters.AddWithValue("@Abbreviation", row.Cells(3).Value)
                    cmd.ExecuteNonQuery()
                Next

                Trans.Commit()
                cnn.Close()

                MsgBox("Updated Successfully!", MsgBoxStyle.Information, Me.Text)

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try

        ElseIf e.ColumnIndex = Me.DGColDelete.Index Then
            If MsgBox("Confirm delete?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
                Try
                    Me.Cursor = Cursors.WaitCursor

                    Dim cmd As New SqlCommand("Delete From InsurClasses Where SNo=" & Me.GridInsuranceClasses.CurrentRow.Cells(0).Value, cnn)

                    cnn.Open()
                    cmd.ExecuteNonQuery()
                    cnn.Close()

                    FillClasses()

                    Me.Cursor = Cursors.Default
                Catch ex As Exception
                    Me.Cursor = Cursors.Default
                    If cnn.State = ConnectionState.Open Then
                        cnn.Close()
                    End If
                    MsgBox(ex.ToString)
                End Try
            End If
        End If
    End Sub

    Private Sub btnClear_Click(sender As System.Object, e As System.EventArgs) Handles btnClear.Click
        Clear()
    End Sub

    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles btnSelectAcc.Click
        Dim a As New frmSelectAccount
        a.ShowDialog()

        If SelAccNo <> "" Then
            Me.txtAccNo.Text = SelAccNo
            Me.txtAccName.Text = SelAccName
        End If
    End Sub

    Private Sub txtAccNo_KeyUp(sender As Object, e As System.Windows.Forms.KeyEventArgs) Handles txtAccNo.KeyUp
        If e.KeyCode = Keys.Enter Then
            Me.txtAccName.Text = GetAccName(Me.txtAccNo.Text.Trim)
        End If
    End Sub

    Private Sub txtAccNo_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtAccNo.TextChanged
        Me.txtAccName.Clear()
    End Sub

    Private Sub GridInsuranceClasses_CellEndEdit(sender As System.Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridInsuranceClasses.CellEndEdit
        If e.ColumnIndex = Me.DGColAccNo.Index Then
            Try
                If Me.GridInsuranceClasses.CurrentCell.Value <> "" Then
                    Me.Cursor = Cursors.WaitCursor

                    Me.GridInsuranceClasses.CurrentRow.Cells(Me.DGColAccName.Index).Value = _
                        GetAccName(Me.GridInsuranceClasses.CurrentRow.Cells(Me.DGColAccNo.Index).Value)

                    Me.Cursor = Cursors.Default
                Else
                    Me.GridInsuranceClasses.CurrentRow.Cells(Me.DGColAccNo.Index).Value = "-"
                    Me.GridInsuranceClasses.CurrentRow.Cells(Me.DGColAccName.Index).Value = ""
                End If
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                MsgBox(ex.ToString)
            End Try
        End If

        CheckColour()
    End Sub

    Sub CheckColour()
        For Each row As DataGridViewRow In Me.GridInsuranceClasses.Rows
            If row.Cells(Me.DGColAccName.Index).Value Is Nothing Or row.Cells(Me.DGColAccName.Index).Value = "" Then
                row.Cells(Me.DGColAccName.Index).Style.BackColor = Color.Salmon
            Else
                row.Cells(Me.DGColAccName.Index).Style.BackColor = row.DefaultCellStyle.BackColor
            End If
        Next
    End Sub
End Class